/*
 * 
 * 
 * 
 */
package com.hboxs.asl.dao.impl;

import com.hboxs.asl.Filter;
import com.hboxs.asl.Page;
import com.hboxs.asl.Pageable;
import com.hboxs.asl.Setting;
import com.hboxs.asl.dao.GoodsDao;
import com.hboxs.asl.dao.ProductDao;
import com.hboxs.asl.dao.SnDao;
import com.hboxs.asl.entity.*;
import com.hboxs.asl.entity.Order;
import com.hboxs.asl.util.SettingUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import javax.annotation.Resource;
import javax.persistence.FlushModeType;
import javax.persistence.NoResultException;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import java.math.BigDecimal;
import java.util.*;
import java.util.Map.Entry;
import java.util.regex.Pattern;

/**
 * Dao - 商品
 */
@Repository("productDaoImpl")
public class ProductDaoImpl extends BaseDaoImpl<Product, Long> implements ProductDao {

    private static final Pattern pattern = Pattern.compile("\\d*");
    private static final Integer SEARCHCATEGORY = 200;

    @Resource(name = "goodsDaoImpl")
    private GoodsDao goodsDao;
    @Resource(name = "snDaoImpl")
    private SnDao snDao;

    public boolean snExists(String sn) {
        if (sn == null) {
            return false;
        }
        String jpql = "select count(product) from Product product where lower(product.sn) = lower(:sn)";
        Long count = entityManager.createQuery(jpql, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("sn", sn).getSingleResult();
        return count > 0;
    }

    public Product findBySn(String sn) {
        if (sn == null) {
            return null;
        }
        String jpql = "select product from Product product where lower(product.sn) = lower(:sn)";
        try {
            return entityManager.createQuery(jpql, Product.class).setFlushMode(FlushModeType.COMMIT).setParameter("sn", sn).getSingleResult();
        } catch (NoResultException e) {
            return null;
        }
    }

    public List<Product> search(String keyword, Integer count) {
        if (StringUtils.isEmpty(keyword)) {
            return Collections.emptyList();
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (pattern.matcher(keyword).matches()) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("id"), Long.valueOf(keyword)),
                    criteriaBuilder.like(root.<String>get("sn"), "%" + keyword + "%"), criteriaBuilder.like(root.<String>get("name"), "%" + keyword + "%")));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.like(root.<String>get("sn"), "%" + keyword + "%"),
                    criteriaBuilder.like(root.<String>get("name"), "%" + keyword + "%")));
        }
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
        return super.findList(criteriaQuery, null, count, null, null);
    }

    public List<Product> searchGroup(String keyword, Integer count) {
        if (StringUtils.isEmpty(keyword)) {
            return Collections.emptyList();
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.groupBy(root.get("goods"));
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (pattern.matcher(keyword).matches()) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.equal(root.get("id"), Long.valueOf(keyword)),
                    criteriaBuilder.like(root.<String>get("sn"), "%" + keyword + "%"), criteriaBuilder.like(root.<String>get("name"), "%" + keyword + "%")));
        } else {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.like(root.<String>get("sn"), "%" + keyword + "%"),
                    criteriaBuilder.like(root.<String>get("name"), "%" + keyword + "%")));
        }
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
        return super.findList(criteriaQuery, null, count, null, null);
    }

    public List<Product> findList(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags, Map<Attribute, String> attributeValue, BigDecimal startPrice,
                                  BigDecimal endPrice, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, Product.OrderType orderType,
                                  Integer count, List<Filter> filters, List<com.hboxs.asl.Order> orders) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (productCategory != null) {
            restrictions = criteriaBuilder.and(
                    restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.equal(root.get("productCategory"), productCategory),
                            criteriaBuilder.like(root.get("productCategory").<String>get("treePath"), "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                    + ProductCategory.TREE_PATH_SEPARATOR + "%")));
        }
        if (brand != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
        }
        if (promotion != null) {
            Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot1 = subquery1.from(Product.class);
            subquery1.select(subqueryRoot1);
            subquery1.where(criteriaBuilder.equal(subqueryRoot1, root), criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

            Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot2 = subquery2.from(Product.class);
            subquery2.select(subqueryRoot2);
            subquery2.where(criteriaBuilder.equal(subqueryRoot2, root), criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

            Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot3 = subquery3.from(Product.class);
            subquery3.select(subqueryRoot3);
            subquery3.where(criteriaBuilder.equal(subqueryRoot3, root), criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.exists(subquery1), criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
        }
        if (tags != null && !tags.isEmpty()) {
            Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot = subquery.from(Product.class);
            subquery.select(subqueryRoot);
            subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
        }
        if (attributeValue != null) {
            for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
                String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX + entry.getKey().getPropertyIndex();
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get(propertyName), entry.getValue()));
            }
        }
        if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
            BigDecimal temp = startPrice;
            startPrice = endPrice;
            endPrice = temp;
        }
        if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.ge(root.<Number>get("price"), startPrice));
        }
        if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.le(root.<Number>get("price"), endPrice));
        }
        if (isMarketable != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
        }
        if (isList != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
        }
        if (isTop != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
        }
        Path<Integer> stock = root.get("stock");
        Path<Integer> allocatedStock = root.get("allocatedStock");
        if (isOutOfStock != null) {
            if (isOutOfStock) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, allocatedStock)));
            }
        }
        if (isStockAlert != null) {
            Setting setting = SettingUtils.get();
            if (isStockAlert) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                        criteriaBuilder.lessThanOrEqualTo(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
            } else {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
            }
        }
        criteriaQuery.where(restrictions);
        if (orderType == Product.OrderType.priceAsc) {
            orders.add(com.hboxs.asl.Order.asc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.priceDesc) {
            orders.add(com.hboxs.asl.Order.desc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.salesDesc) {
            orders.add(com.hboxs.asl.Order.desc("sales"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.dateDesc) {
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else {
            orders.add(com.hboxs.asl.Order.desc("isTop"));
            orders.add(com.hboxs.asl.Order.desc("modifyDate"));
        }
        return super.findList(criteriaQuery, null, count, filters, orders);
    }

    public List<Product> findList(ProductCategory productCategory, Date beginDate, Date endDate, Integer first, Integer count) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), true));
        if (productCategory != null) {
            restrictions = criteriaBuilder.and(
                    restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.equal(root.get("productCategory"), productCategory),
                            criteriaBuilder.like(root.get("productCategory").<String>get("treePath"), "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                    + ProductCategory.TREE_PATH_SEPARATOR + "%")));
        }
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
        }
        criteriaQuery.where(restrictions);
        criteriaQuery.orderBy(criteriaBuilder.desc(root.get("isTop")));
        return super.findList(criteriaQuery, first, count, null, null);
    }

    public List<Product> findList(Goods goods, Set<Product> excludes) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (goods != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("goods"), goods));
        }
        if (excludes != null && !excludes.isEmpty()) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.not(root.in(excludes)));
        }
        criteriaQuery.where(restrictions);
        return entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT).getResultList();
    }

    public List<Object[]> findSalesList(Date beginDate, Date endDate, Integer count) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
        Root<Product> product = criteriaQuery.from(Product.class);
        Join<Product, OrderItem> orderItems = product.join("orderItems");
        Join<Product, com.hboxs.asl.entity.Order> order = orderItems.join("order");
        criteriaQuery.multiselect(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"),
                criteriaBuilder.sum(orderItems.<Integer>get("quantity")),
                criteriaBuilder.sum(criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price"))));
        Predicate restrictions = criteriaBuilder.conjunction();
        if (beginDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.greaterThanOrEqualTo(order.<Date>get("createDate"), beginDate));
        }
        if (endDate != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.lessThanOrEqualTo(order.<Date>get("createDate"), endDate));
        }
        restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(order.get("orderStatus"), Order.OrderStatus.completed),
                criteriaBuilder.equal(order.get("paymentStatus"), Order.PaymentStatus.paid));
        criteriaQuery.where(restrictions);
        criteriaQuery.groupBy(product.get("id"), product.get("sn"), product.get("name"), product.get("fullName"), product.get("price"));
        criteriaQuery.orderBy(criteriaBuilder.desc(criteriaBuilder.sum(criteriaBuilder.prod(orderItems.<Integer>get("quantity"), orderItems.<BigDecimal>get("price")))));
        TypedQuery<Object[]> query = entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
        if (count != null && count >= 0) {
            query.setMaxResults(count);
        }
        return query.getResultList();
    }

    public Page<Product> findPage(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags, Map<Attribute, String> attributeValue, BigDecimal startPrice,
                                  BigDecimal endPrice, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, Product.OrderType orderType,
                                  Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (productCategory != null) {
            restrictions = criteriaBuilder.and(
                    restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.equal(root.get("productCategory"), productCategory),
                            criteriaBuilder.like(root.get("productCategory").<String>get("treePath"), "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                    + ProductCategory.TREE_PATH_SEPARATOR + "%")));
        }
        if (brand != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
        }
        if (promotion != null) {
            Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot1 = subquery1.from(Product.class);
            subquery1.select(subqueryRoot1);
            subquery1.where(criteriaBuilder.equal(subqueryRoot1, root), criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

            Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot2 = subquery2.from(Product.class);
            subquery2.select(subqueryRoot2);
            subquery2.where(criteriaBuilder.equal(subqueryRoot2, root), criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

            Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot3 = subquery3.from(Product.class);
            subquery3.select(subqueryRoot3);
            subquery3.where(criteriaBuilder.equal(subqueryRoot3, root), criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.exists(subquery1), criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
        }
        if (tags != null && !tags.isEmpty()) {
            Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot = subquery.from(Product.class);
            subquery.select(subqueryRoot);
            subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
        }
        if (attributeValue != null) {
            // 横向多选or
            Predicate cbOr = null;
            // 纵向多个选择组合and
            Predicate cbAnd = criteriaBuilder.conjunction();
            ;
            for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
                String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX + entry.getKey().getPropertyIndex();
                String[] entrys = entry.getValue().split(",");
                cbOr = criteriaBuilder.disjunction();
                for (int i = 0; i < entrys.length; i++) {
                    if (entrys[i] != null && !StringUtils.isEmpty(entrys[i])) {
                        cbOr = criteriaBuilder.or(cbOr, criteriaBuilder.like(root.<String>get(propertyName), "%" + entrys[i] + "%"));
                    }
                }
                cbAnd = criteriaBuilder.and(cbAnd, cbOr);
            }
            restrictions = criteriaBuilder.and(restrictions, cbAnd);
        }
        if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
            BigDecimal temp = startPrice;
            startPrice = endPrice;
            endPrice = temp;
        }
        if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.ge(root.<Number>get("price"), startPrice));
        }
        if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.le(root.<Number>get("price"), endPrice));
        }
        if (isMarketable != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
        }
        if (isList != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
        }
        if (isTop != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
        }
        Path<Integer> stock = root.get("stock");
        Path<Integer> allocatedStock = root.get("allocatedStock");
        if (isOutOfStock != null) {
            if (isOutOfStock) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, allocatedStock)));
            }
        }
        if (isStockAlert != null) {
            Setting setting = SettingUtils.get();
            if (isStockAlert) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                        criteriaBuilder.lessThanOrEqualTo(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
            } else {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
            }
        }
        criteriaQuery.where(restrictions);
        List<com.hboxs.asl.Order> orders = pageable.getOrders();
        if (orderType == Product.OrderType.priceAsc) {
            orders.add(com.hboxs.asl.Order.asc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.priceDesc) {
            orders.add(com.hboxs.asl.Order.desc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.salesDesc) {
            orders.add(com.hboxs.asl.Order.desc("sales"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.dateDesc) {
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else {
            orders.add(com.hboxs.asl.Order.desc("isTop"));
            orders.add(com.hboxs.asl.Order.desc("modifyDate"));
        }
        return super.findPage(criteriaQuery, pageable);
    }

    public Page<Product> findPageGroup(ProductCategory productCategory, Brand brand, Promotion promotion, List<Tag> tags, Map<Attribute, String> attributeValue, BigDecimal startPrice,
                                       BigDecimal endPrice, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert, Product.OrderType orderType,
                                       Pageable pageable) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.groupBy(root.get("goods"));
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (productCategory != null) {
            restrictions = criteriaBuilder.and(
                    restrictions,
                    criteriaBuilder.or(
                            criteriaBuilder.equal(root.get("productCategory"), productCategory),
                            criteriaBuilder.like(root.get("productCategory").<String>get("treePath"), "%" + ProductCategory.TREE_PATH_SEPARATOR + productCategory.getId()
                                    + ProductCategory.TREE_PATH_SEPARATOR + "%")));
        }
        if (brand != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("brand"), brand));
        }
        if (promotion != null) {
            Subquery<Product> subquery1 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot1 = subquery1.from(Product.class);
            subquery1.select(subqueryRoot1);
            subquery1.where(criteriaBuilder.equal(subqueryRoot1, root), criteriaBuilder.equal(subqueryRoot1.join("promotions"), promotion));

            Subquery<Product> subquery2 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot2 = subquery2.from(Product.class);
            subquery2.select(subqueryRoot2);
            subquery2.where(criteriaBuilder.equal(subqueryRoot2, root), criteriaBuilder.equal(subqueryRoot2.join("productCategory").join("promotions"), promotion));

            Subquery<Product> subquery3 = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot3 = subquery3.from(Product.class);
            subquery3.select(subqueryRoot3);
            subquery3.where(criteriaBuilder.equal(subqueryRoot3, root), criteriaBuilder.equal(subqueryRoot3.join("brand").join("promotions"), promotion));

            restrictions = criteriaBuilder.and(restrictions,
                    criteriaBuilder.or(criteriaBuilder.exists(subquery1), criteriaBuilder.exists(subquery2), criteriaBuilder.exists(subquery3)));
        }
        if (tags != null && !tags.isEmpty()) {
            Subquery<Product> subquery = criteriaQuery.subquery(Product.class);
            Root<Product> subqueryRoot = subquery.from(Product.class);
            subquery.select(subqueryRoot);
            subquery.where(criteriaBuilder.equal(subqueryRoot, root), subqueryRoot.join("tags").in(tags));
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.exists(subquery));
        }
        if (attributeValue != null) {
            // 横向多选or
            Predicate cbOr = null;
            // 纵向多个选择组合and
            Predicate cbAnd = criteriaBuilder.conjunction();
            ;
            for (Entry<Attribute, String> entry : attributeValue.entrySet()) {
                String propertyName = Product.ATTRIBUTE_VALUE_PROPERTY_NAME_PREFIX + entry.getKey().getPropertyIndex();
                String[] entrys = entry.getValue().split(",");
                cbOr = criteriaBuilder.disjunction();
                for (int i = 0; i < entrys.length; i++) {
                    if (entrys[i] != null && !StringUtils.isEmpty(entrys[i])) {
                        cbOr = criteriaBuilder.or(cbOr, criteriaBuilder.like(root.<String>get(propertyName), "%" + entrys[i] + "%"));
                    }
                }
                cbAnd = criteriaBuilder.and(cbAnd, cbOr);
            }
            restrictions = criteriaBuilder.and(restrictions, cbAnd);
        }
        if (startPrice != null && endPrice != null && startPrice.compareTo(endPrice) > 0) {
            BigDecimal temp = startPrice;
            startPrice = endPrice;
            endPrice = temp;
        }
        if (startPrice != null && startPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.ge(root.<Number>get("price"), startPrice));
        }
        if (endPrice != null && endPrice.compareTo(new BigDecimal(0)) >= 0) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.le(root.<Number>get("price"), endPrice));
        }
        if (isMarketable != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
        }
        if (isList != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
        }
        if (isTop != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
        }
        Path<Integer> stock = root.get("stock");
        Path<Integer> allocatedStock = root.get("allocatedStock");
        if (isOutOfStock != null) {
            if (isOutOfStock) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, allocatedStock)));
            }
        }
        if (isStockAlert != null) {
            Setting setting = SettingUtils.get();
            if (isStockAlert) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                        criteriaBuilder.lessThanOrEqualTo(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
            } else {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
            }
        }
        criteriaQuery.where(restrictions);
        List<com.hboxs.asl.Order> orders = pageable.getOrders();
        if (orderType == Product.OrderType.priceAsc) {
            orders.add(com.hboxs.asl.Order.asc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.priceDesc) {
            orders.add(com.hboxs.asl.Order.desc("price"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.salesDesc) {
            orders.add(com.hboxs.asl.Order.desc("sales"));
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else if (orderType == Product.OrderType.dateDesc) {
            orders.add(com.hboxs.asl.Order.desc("createDate"));
        } else {
            orders.add(com.hboxs.asl.Order.desc("isTop"));
            orders.add(com.hboxs.asl.Order.asc("order"));
        }
        return super.findPageGroup(criteriaQuery, pageable);
    }

    public Page<Product> findPage(Member member, Pageable pageable) {
        if (member == null) {
            return new Page<Product>(Collections.<Product>emptyList(), 0, pageable);
        }
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        criteriaQuery.where(criteriaBuilder.equal(root.join("favoriteMembers"), member));
        return super.findPage(criteriaQuery, pageable);
    }

    public Long count(Member favoriteMember, Boolean isMarketable, Boolean isList, Boolean isTop, Boolean isOutOfStock, Boolean isStockAlert) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
        Root<Product> root = criteriaQuery.from(Product.class);
        criteriaQuery.select(root);
        Predicate restrictions = criteriaBuilder.conjunction();
        if (favoriteMember != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.join("favoriteMembers"), favoriteMember));
        }
        if (isMarketable != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
        }
        if (isList != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isList"), isList));
        }
        if (isTop != null) {
            restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isTop"), isTop));
        }
        Path<Integer> stock = root.get("stock");
        Path<Integer> allocatedStock = root.get("allocatedStock");
        if (isOutOfStock != null) {
            if (isOutOfStock) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock), criteriaBuilder.lessThanOrEqualTo(stock, allocatedStock));
            } else {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, allocatedStock)));
            }
        }
        if (isStockAlert != null) {
            Setting setting = SettingUtils.get();
            if (isStockAlert) {
                restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.isNotNull(stock),
                        criteriaBuilder.lessThanOrEqualTo(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount())));
            } else {
                restrictions = criteriaBuilder.and(restrictions,
                        criteriaBuilder.or(criteriaBuilder.isNull(stock), criteriaBuilder.greaterThan(stock, criteriaBuilder.sum(allocatedStock, setting.getStockAlertCount()))));
            }
        }
        criteriaQuery.where(restrictions);
        return super.count(criteriaQuery, null);
    }

    public boolean isPurchased(Member member, Product product) {
        if (member == null || product == null) {
            return false;
        }
        String jqpl = "select count(orderItem) from OrderItem orderItem where orderItem.product = :product and orderItem.order.member = :member and orderItem.order.orderStatus = :orderStatus";
        Long count = entityManager.createQuery(jqpl, Long.class).setFlushMode(FlushModeType.COMMIT).setParameter("product", product).setParameter("member", member)
                .setParameter("orderStatus", Order.OrderStatus.completed).getSingleResult();
        return count > 0;
    }

    @Override
    public Boolean isFavorite(Product product, Member member) {
        String sqlStr = "select * from xx_member_favorite_product  where favorite_members=" + member.getId() + " AND favorite_products=" + product.getId();
        List result = entityManager.createNativeQuery(sqlStr).getResultList();
        return result.size() > 0;
    }

    @Override
    public List<Product> findListSortByPrice(ProductCategory productCategory, Boolean isMarketable, Pageable pageable) {
        String jql = "";
        Query query = null;
        if (productCategory == null) {
            jql = "SELECT product, " +
                    "CASE " +
                    "   WHEN product.promotionPrice is null THEN product.price " +
                    "   ELSE product.promotionPrice END AS curprice " +
                    "FROM Product AS product " +
                    "WHERE product.isMarketable=:isMarketable " +
                    "ORDER BY curprice DESC";
            query = entityManager.createQuery(jql).setParameter("isMarketable", isMarketable);

        } else {
            jql = "SELECT product, " +
                    "CASE " +
                    "   WHEN product.promotionPrice is null THEN product.price " +
                    "   ELSE product.promotionPrice END AS curprice " +
                    "FROM Product AS product " +
                    "WHERE product.productCategory=:productCategory AND product.isMarketable=:isMarketable " +
                    "ORDER BY curprice DESC";
            query = entityManager.createQuery(jql).setParameter("productCategory", productCategory).setParameter("isMarketable", isMarketable);
        }
        List result = query.setFirstResult((pageable.getPageNumber() - 1) * pageable.getPageSize()).setMaxResults(pageable.getPageSize()).getResultList();
        List<Product> products = new ArrayList<>();
        for (Object object : result) {
            Object[] objects = (Object[]) object;
            products.add((Product) objects[0]);
        }
        return products;
    }

    /**
     * 设置值并保存
     *
     * @param product 商品
     */
    @Override
    public void persist(Product product) {
        Assert.notNull(product);

        setValue(product);
        super.persist(product);
    }

    /**
     * 设置值并更新
     *
     * @param product 商品
     * @return 商品
     */
    @Override
    public Product merge(Product product) {
        Assert.notNull(product);

        if (!product.getIsMarketable()) {
            String jpql = "delete from CartItem cartItem where cartItem.product = :product";
            entityManager.createQuery(jpql).setFlushMode(FlushModeType.COMMIT).setParameter("product", product).executeUpdate();
        }
        setValue(product);
        return super.merge(product);
    }

    @Override
    public void remove(Product product) {
        if (product != null) {
            Goods goods = product.getGoods();
            if (goods != null && goods.getProducts() != null) {
                goods.getProducts().remove(product);
                if (goods.getProducts().isEmpty()) {
                    goodsDao.remove(goods);
                }
            }
        }
        super.remove(product);
    }

    /**
     * 设置值
     *
     * @param product 商品
     */
    private void setValue(Product product) {
        if (product == null) {
            return;
        }
        if (StringUtils.isEmpty(product.getSn())) {
            String sn;
            do {
                sn = snDao.generate(Sn.Type.product);
            } while (snExists(sn));
            product.setSn(sn);
        }
//		StringBuffer fullName = new StringBuffer(product.getName());
//		if (product.getSpecificationValues() != null && !product.getSpecificationValues().isEmpty()) {
//			List<SpecificationValue> specificationValues = new ArrayList<SpecificationValue>(product.getSpecificationValues());
//			Collections.sort(specificationValues, new Comparator<SpecificationValue>() {
//				public int compare(SpecificationValue a1, SpecificationValue a2) {
//					return new CompareToBuilder().append(a1.getSpecification(), a2.getSpecification()).toComparison();
//				}
//			});
//			fullName.append(Product.FULL_NAME_SPECIFICATION_PREFIX);
//			int i = 0;
//			for (Iterator<SpecificationValue> iterator = specificationValues.iterator(); iterator.hasNext(); i++) {
//				if (i != 0) {
//					fullName.append(Product.FULL_NAME_SPECIFICATION_SEPARATOR);
//				}
//				fullName.append(iterator.next().getName());
//			}
//			fullName.append(Product.FULL_NAME_SPECIFICATION_SUFFIX);
//		}
//		product.setName(fullName.toString());
    }

}